<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
 <head>
  <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  <title>Pattern based caching</title>

 </head>
 <body><div class="manualnavbar" style="text-align: center;">
 <div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-qc.per-query-ttl.html">Setting the TTL</a></div>
 <div class="next" style="text-align: right; float: right;"><a href="mysqlnd-qc.slam-defense.html">Slam defense</a></div>
 <div class="up"><a href="mysqlnd-qc.quickstart.html">Quickstart and Examples</a></div>
 <div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="mysqlnd-qc.pattern-based-caching" class="section">
  <h2 class="title">Pattern based caching</h2>
  <p class="para">
   An application has three options for telling PECL/mysqlnd_qc whether a particular
   statement shall be used. The most basic approach is to cache all statements by
   setting <em><a href="mysqlnd-qc.configuration.html" class="link">
   mysqlnd_qc.cache_by_default = 1</a></em>. This approach is often of little
   practical value. But it enables users to make a quick estimation about
   the maximum performance gains from caching. An application designed to
   use a cache may be able to prefix selected statements with the appropriate SQL
   hints. However, altering an applications source code may not always be possible
   or desired, for example, to avoid problems with software updates. Therefore,
   PECL/mysqlnd_qc allows setting a callback which decides if a query is to be
   cached.
  </p>
  <p class="para">
   The callback is installed with the <span class="function"><a href="function.mysqlnd-qc-set-is-select.html" class="function">mysqlnd_qc_set_is_select()</a></span>
   function. The callback is given the statement string of every statement
   inspected by the plugin. Then, the callback can decide whether to cache
   the function. The callback is supposed to return <strong><code>FALSE</code></strong>
   if the statement shall not be cached. A return value of <strong><code>TRUE</code></strong>
   makes the plugin try to add the statement into the cache. The cache entry
   will be given the default TTL (<em><a href="mysqlnd-qc.configuration.html" class="link">
   mysqlnd_qc.ttl</a></em>). If the callback returns
   a numerical value it is used as the TTL instead of the global default.
  </p>
  <p class="para">
   <div class="example" id="example-1823">
    <p><strong>Example #1 Setting a callback with <span class="function"><a href="function.mysqlnd-qc-set-is-select.html" class="function">mysqlnd_qc_set_is_select()</a></span></strong></p>
    <div class="example-contents">
<div class="inicode"><pre class="inicode">mysqlnd_qc.enable_qc=1
mysqlnd_qc.collect_statistics=1</pre>
</div>
    </div>

    <div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB">&lt;?php<br /></span><span style="color: #FF8000">/*&nbsp;callback&nbsp;which&nbsp;decides&nbsp;if&nbsp;query&nbsp;is&nbsp;cached&nbsp;*/<br /></span><span style="color: #007700">function&nbsp;</span><span style="color: #0000BB">is_select</span><span style="color: #007700">(</span><span style="color: #0000BB">$query</span><span style="color: #007700">)&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;static&nbsp;</span><span style="color: #0000BB">$patterns&nbsp;</span><span style="color: #007700">=&nbsp;array(<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #FF8000">/*&nbsp;true&nbsp;-&nbsp;use&nbsp;default&nbsp;from&nbsp;mysqlnd_qc.ttl&nbsp;*/<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #DD0000">"@SELECT\s+.*\s+FROM\s+test@ismU"&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #0000BB">true</span><span style="color: #007700">,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #FF8000">/*&nbsp;3&nbsp;-&nbsp;use&nbsp;TTL&nbsp;=&nbsp;3&nbsp;seconds&nbsp;*/<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #DD0000">"@SELECT\s+.*\s+FROM\s+news@ismU"&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #0000BB">3<br />&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #007700">);<br /><br />&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #FF8000">/*&nbsp;check&nbsp;if&nbsp;query&nbsp;does&nbsp;match&nbsp;pattern&nbsp;*/<br />&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #007700">foreach&nbsp;(</span><span style="color: #0000BB">$patterns&nbsp;</span><span style="color: #007700">as&nbsp;</span><span style="color: #0000BB">$pattern&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #0000BB">$ttl</span><span style="color: #007700">)&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;(</span><span style="color: #0000BB">preg_match</span><span style="color: #007700">(</span><span style="color: #0000BB">$pattern</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$query</span><span style="color: #007700">))&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"is_select(%45s):&nbsp;cache\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$query</span><span style="color: #007700">);<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;return&nbsp;</span><span style="color: #0000BB">$ttl</span><span style="color: #007700">;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}<br />&nbsp;&nbsp;&nbsp;&nbsp;}<br />&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"is_select(%45s):&nbsp;do&nbsp;not&nbsp;cache\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$query</span><span style="color: #007700">);<br />&nbsp;&nbsp;&nbsp;&nbsp;return&nbsp;</span><span style="color: #0000BB">false</span><span style="color: #007700">;<br />}<br /></span><span style="color: #FF8000">/*&nbsp;install&nbsp;callback&nbsp;*/<br /></span><span style="color: #0000BB">mysqlnd_qc_set_is_select</span><span style="color: #007700">(</span><span style="color: #DD0000">"is_select"</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">/*&nbsp;Connect,&nbsp;create&nbsp;and&nbsp;populate&nbsp;test&nbsp;table&nbsp;*/<br /></span><span style="color: #0000BB">$mysqli&nbsp;</span><span style="color: #007700">=&nbsp;new&nbsp;</span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"host"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"user"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"password"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"schema"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"port"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"socket"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"DROP&nbsp;TABLE&nbsp;IF&nbsp;EXISTS&nbsp;test"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"CREATE&nbsp;TABLE&nbsp;test(id&nbsp;INT)"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"INSERT&nbsp;INTO&nbsp;test(id)&nbsp;VALUES&nbsp;(1),&nbsp;(2),&nbsp;(3)"</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">/*&nbsp;cache&nbsp;put&nbsp;*/<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT&nbsp;id&nbsp;FROM&nbsp;test&nbsp;WHERE&nbsp;id&nbsp;=&nbsp;1"</span><span style="color: #007700">);<br /></span><span style="color: #FF8000">/*&nbsp;cache&nbsp;hit&nbsp;*/<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT&nbsp;id&nbsp;FROM&nbsp;test&nbsp;WHERE&nbsp;id&nbsp;=&nbsp;1"</span><span style="color: #007700">);<br /></span><span style="color: #FF8000">/*&nbsp;cache&nbsp;put&nbsp;*/<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT&nbsp;*&nbsp;FROM&nbsp;test"</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$stats&nbsp;</span><span style="color: #007700">=&nbsp;</span><span style="color: #0000BB">mysqlnd_qc_get_core_stats</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"Cache&nbsp;put:&nbsp;%d\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$stats</span><span style="color: #007700">[</span><span style="color: #DD0000">'cache_put'</span><span style="color: #007700">]);<br /></span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"Cache&nbsp;hit:&nbsp;%d\n"</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$stats</span><span style="color: #007700">[</span><span style="color: #DD0000">'cache_hit'</span><span style="color: #007700">]);<br /></span><span style="color: #0000BB">?&gt;</span>
</span>
</code></div>
    </div>

   <div class="example-contents"><p>以上例程的输出类似于：</p></div>
    <div class="example-contents screen">
<div class="cdata"><pre>
is_select(                    DROP TABLE IF EXISTS test): do not cache
is_select(                    CREATE TABLE test(id INT)): do not cache
is_select(    INSERT INTO test(id) VALUES (1), (2), (3)): do not cache
is_select(             SELECT id FROM test WHERE id = 1): cache
is_select(             SELECT id FROM test WHERE id = 1): cache
is_select(                           SELECT * FROM test): cache
Cache put: 2
Cache hit: 1
</pre></div>
    </div>
   </div>
  </p>
  <p class="para">
   The examples callback tests if a statement string matches a pattern.
   If this is the case, it either returns <strong><code>TRUE</code></strong> to cache
   the statement using the global default TTL or an alternative TTL.
  </p>
  <p class="para">
   To minimize application changes the callback can put into and registered
   in an auto prepend file.
  </p>
 </div><hr /><div class="manualnavbar" style="text-align: center;">
 <div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-qc.per-query-ttl.html">Setting the TTL</a></div>
 <div class="next" style="text-align: right; float: right;"><a href="mysqlnd-qc.slam-defense.html">Slam defense</a></div>
 <div class="up"><a href="mysqlnd-qc.quickstart.html">Quickstart and Examples</a></div>
 <div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
